unit Criteria;

interface

type

  TCriteria = class
  private
    fQuery: String;
    fDistinct: boolean;
    fFields: String;
    fTableName: String;
    fPrimaryKey: String;
    fAdditionalConditions: string;
    fConditions: String;
    fSearchConditions: String;
    fOrderBys: String;
    fGroupBys: String;
    fHavings: String;
  public
    function getQuery(): String;
    constructor Create(aTableName: String);
    function getConditions(): String;

    function addStrCondition(aField: String; aValue: String;
      aSign: String = '='; aOperator: String = 'AND'): TCriteria;
    class function getStrCondition(aField: String; aValue: String;
      aSign: String = '='): String;
    function addIntCondition(aField: String; aValue: integer;
      aSign: String = '='; aOperator: String = 'AND'): TCriteria;
    class function getIntCondition(aField: String; aValue: integer;
      aSign: String = '='): String;
    function addFloatCondition(aField: String; aValue: real;
      aSign: String = '='; aOperator: String = 'AND'): TCriteria;
    class function getFloatCondition(aField: String; aValue: real;
      aSign: String = '='): String;
    function addBoolCondition(aField: String; aValue: boolean;
      aSign: String = '='; aOperator: String = 'AND'): TCriteria;
    class function getBoolCondition(aField: String; aValue: boolean;
      aSign: String = '='): String;
    function addDateCondition(aField: String; aValue: TDateTime;
      aSign: String = '='; aOperator: String = 'AND'): TCriteria;
    class function getDateCondition(aField: String; aValue: TDateTime;
      aSign: String = '='): String;
    function addIsNullCondition(aField: String; aOperator: String = 'AND')
      : TCriteria;
    class function getIsNullCondition(aField: String): String;
    function addNotIsNullCondition(aField: String; aOperator: String = 'AND')
      : TCriteria;
    class function getNotIsNullCondition(aField: String): String;
    function addStrConditionBetween(aField: String;
      aMinValue, aMaxValue: String; aOperator: String = 'AND'): TCriteria;
    class function getStrConditionBetween(aField: String;
      aMinValue, aMaxValue: String): String;
    function addIntConditionBetween(aField: String;
      aMinValue, aMaxValue: integer; aOperator: String = 'AND'): TCriteria;
    class function getIntConditionBetween(aField: String;
      aMinValue, aMaxValue: integer): String;
    function addFloatConditionBetween(aField: String;
      aMinValue, aMaxValue: real; aOperator: String = 'AND'): TCriteria;
    class function getFloatConditionBetween(aField: String;
      aMinValue, aMaxValue: real): String;
    function addDateConditionBetween(aField: String;
      aMinValue, aMaxValue: TDateTime; aOperator: String = 'AND'): TCriteria;
    class function getDateConditionBetween(aField: String;
      aMinValue, aMaxValue: TDateTime): String;
    function addConditionLike(aField: String; aValue: String;
      aOperator: String = 'AND'): TCriteria;
    class function getConditionLike(aField: String; aValue: String): String;
    function addConditionNotLike(aField: String; aValue: String;
      aOperator: String = 'AND'): TCriteria;
    class function getConditionNotLike(aField: String; aValue: String): String;
  end;

implementation

uses
  SysUtils, StringMangler, SQLFormat;

{ TCriteria }

constructor TCriteria.Create(aTableName: String);
begin
  inherited Create();
  fTableName := aTableName;
end;

function TCriteria.getQuery: String;
begin
  Result := 'SELECT * FROM ' + fTableName;
  if (Trim(fConditions) <> '') then
  begin
    Result := Result + ' WHERE ' + getConditions();
  end;
  Result := Result  + ';'
end;

function TCriteria.addFloatCondition(aField: String; aValue: real;
  aSign, aOperator: String): TCriteria;
begin
  if (Trim(fConditions) <> '') then
  begin
    fConditions := fConditions + ' ' + aOperator + ' ';
  end;
  fConditions := fConditions + '(' + aField + ' ' + aSign + ' ' +
    TStringMangler.FloatToDotStr(aValue) + ')';
  Result := Self;
end;

function TCriteria.addIntCondition(aField: String; aValue: integer;
  aSign, aOperator: String): TCriteria;
begin
  if (Trim(fConditions) <> '') then
  begin
    fConditions := fConditions + ' ' + aOperator + ' ';
  end;
  fConditions := fConditions + '(' + aField + ' ' + aSign + ' ' +
    IntToStr(aValue) + ')';
  Result := Self;
end;

function TCriteria.addStrCondition(aField, aValue, aSign, aOperator: String)
  : TCriteria;
begin
  if (Trim(fConditions) <> '') then
  begin
    fConditions := fConditions + ' ' + aOperator + ' ';
  end;
  fConditions := fConditions + '(' + aField + ' ' + aSign + ' ''' +
    aValue + ''')';
  Result := Self;
end;

function TCriteria.addDateCondition(aField: String; aValue: TDateTime;
  aSign, aOperator: String): TCriteria;
begin
  if (Trim(fConditions) <> '') then
  begin
    fConditions := fConditions + ' ' + aOperator + ' ';
  end;
  fConditions := fConditions + '(' + aField + ' ' + aSign + ' ' +
    TSQLF.Date(aValue) + ')';
  Result := Self;
end;

function TCriteria.addStrConditionBetween(aField, aMinValue, aMaxValue,
  aOperator: String): TCriteria;
begin
  if (Trim(fConditions) <> '') then
  begin
    fConditions := fConditions + ' ' + aOperator + ' ';
  end;
  fConditions := fConditions + '(' + aField + ' BETWEEN ''' + aMinValue +
    ''' AND ''' + aMaxValue + ''')';
  Result := Self;
end;

function TCriteria.addBoolCondition(aField: String; aValue: boolean;
  aSign, aOperator: String): TCriteria;
begin
  if (Trim(fConditions) <> '') then
  begin
    fConditions := fConditions + ' ' + aOperator + ' ';
  end;
  fConditions := fConditions + '(' + aField + ' ' + aSign + ' ' +
    TStringMangler.BoolToStrNum(aValue) + ')';
  Result := Self;
end;

function TCriteria.addFloatConditionBetween(aField: String;
  aMinValue, aMaxValue: real; aOperator: String): TCriteria;
begin
  if (Trim(fConditions) <> '') then
  begin
    fConditions := fConditions + ' ' + aOperator + ' ';
  end;
  fConditions := fConditions + '(' + aField + ' BETWEEN ' +
    TStringMangler.FloatToDotStr(aMinValue) + ' AND ' + TStringMangler.FloatToDotStr(aMaxValue) + ')';
  Result := Self;
end;

function TCriteria.addIntConditionBetween(aField: String;
  aMinValue, aMaxValue: integer; aOperator: String): TCriteria;
begin
  if (Trim(fConditions) <> '') then
  begin
    fConditions := fConditions + ' ' + aOperator + ' ';
  end;
  fConditions := fConditions + '(' + aField + ' BETWEEN ' + IntToStr(aMinValue)
    + ' AND ' + IntToStr(aMaxValue) + ')';
  Result := Self;
end;

function TCriteria.addIsNullCondition(aField, aOperator: String): TCriteria;
begin
  if (Trim(fConditions) <> '') then
  begin
    fConditions := fConditions + ' ' + aOperator + ' ';
  end;
  fConditions := fConditions + getIsNullCondition(aField);
  Result := Self;
end;

function TCriteria.addDateConditionBetween(aField: String;
  aMinValue, aMaxValue: TDateTime; aOperator: String): TCriteria;
begin
  if (Trim(fConditions) <> '') then
  begin
    fConditions := fConditions + ' ' + aOperator + ' ';
  end;
  fConditions := fConditions + '(' + aField + ' BETWEEN ' +
    TSQLF.Date(aMinValue) + ' AND ' + TSQLF.Date(aMaxValue) + ')';
  Result := Self;
end;

function TCriteria.addConditionLike(aField, aValue, aOperator: String)
  : TCriteria;
begin
  if (Trim(fConditions) <> '') then
  begin
    fConditions := fConditions + ' ' + aOperator + ' ';
  end;
  fConditions := fConditions + '(' + aField + ' LIKE ''' + aValue + ''')';
  Result := Self;
end;

function TCriteria.addConditionNotLike(aField, aValue, aOperator: String)
  : TCriteria;
begin
  if (Trim(fConditions) <> '') then
  begin
    fConditions := fConditions + ' ' + aOperator + ' ';
  end;
  fConditions := fConditions + '(' + aField + ' NOT LIKE ''' + aValue + ''')';
  Result := Self;
end;

function TCriteria.addNotIsNullCondition(aField, aOperator: String): TCriteria;
begin
  if (Trim(fConditions) <> '') then
  begin
    fConditions := fConditions + ' ' + aOperator + ' ';
  end;
  fConditions := fConditions + getNotIsNullCondition(aField);
  Result := Self;
end;

class function TCriteria.getBoolCondition(aField: String; aValue: boolean;
  aSign: String): String;
begin
  Result := '(' + aField + ' ' + aSign + ' ' + TStringMangler.BoolToStrNum(aValue) + ')';
end;

class function TCriteria.getConditionLike(aField, aValue: String): String;
begin
  Result := '(' + aField + ' LIKE ''' + aValue + ''')';
end;

class function TCriteria.getConditionNotLike(aField, aValue: String): String;
begin
  Result := '(' + aField + ' NOT LIKE ''' + aValue + ''')';
end;

function TCriteria.getConditions: String;
begin
  Result := fConditions;
end;

class function TCriteria.getDateCondition(aField: String; aValue: TDateTime;
  aSign: String): String;
begin
  Result := '(' + aField + ' ' + aSign + ' ' + TSQLF.Date(aValue) + ')';
end;

class function TCriteria.getDateConditionBetween(aField: String;
  aMinValue, aMaxValue: TDateTime): String;
begin
  Result := '(' + aField + ' BETWEEN ' + TSQLF.Date(aMinValue) + ' AND ' +
    TSQLF.Date(aMaxValue) + ')';
end;

class function TCriteria.getFloatCondition(aField: String; aValue: real;
  aSign: String): String;
begin
  Result := '(' + aField + ' ' + aSign + ' ' + TStringMangler.FloatToDotStr(aValue) + ')';
end;

class function TCriteria.getFloatConditionBetween(aField: String;
  aMinValue, aMaxValue: real): String;
begin
  Result := '(' + aField + ' BETWEEN ' + TStringMangler.FloatToDotStr(aMinValue) + ' AND '
    + TStringMangler.FloatToDotStr(aMaxValue) + ')';
end;

class function TCriteria.getIntCondition(aField: String; aValue: integer;
  aSign: String): String;
begin
  Result := '(' + aField + ' ' + aSign + ' ' + IntToStr(aValue) + ')';
end;

class function TCriteria.getIntConditionBetween(aField: String;
  aMinValue, aMaxValue: integer): String;
begin
  Result := '(' + aField + ' BETWEEN ' + IntToStr(aMinValue) + ' AND ' +
    IntToStr(aMaxValue) + ')';
end;

class function TCriteria.getIsNullCondition(aField: String): String;
begin
  Result := '(' + aField + ' IS NULL)';
end;

class function TCriteria.getNotIsNullCondition(aField: String): String;
begin
  Result := '(' + aField + ' IS NOT NULL)';
end;

class function TCriteria.getStrCondition(aField, aValue, aSign: String): String;
begin
  Result := '(' + aField + ' ' + aSign + ' ''' + aValue + ''')';
end;

class function TCriteria.getStrConditionBetween(aField, aMinValue,
  aMaxValue: String): String;
begin
  Result := '(' + aField + ' BETWEEN ''' + aMinValue + ''' AND ''' +
    aMaxValue + ''')';
end;

end.
